<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page language="java" import="net.sf.json.JSONArray" %>
<%@ page language="java" import="net.sf.json.JSONObject" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()){
String newAccountName = null;
String newISBN = null;
String newTransDate = null;
String newBookTitle = null;
String newEdition = null;
String newCourses = null;
String newAuthors = null;
String newPublisher = null;
JSONArray arrayObj=new JSONArray();

newAccountName = request.getParameter("username");

Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT * FROM WishList WHERE AccountName = '" + newAccountName + "' AND Status = 'Listed';";
rs0 = statement0.executeQuery(query0);
while(rs0.next()) {
newISBN = rs0.getString("WishList.ISBN");
newTransDate = rs0.getString("WishList.TransDate");

Statement statement1 = null;
ResultSet rs1 = null;
statement1 = connection.createStatement();
String query1 = "SELECT * FROM Books WHERE ISBN = '" + newISBN + "';";
rs1 = statement1.executeQuery(query1);
if(rs1.next()) {
newBookTitle = rs1.getString("Books.BookTitle");
newEdition = rs1.getString("Books.Edition");
newCourses = rs1.getString("Books.Courses");
newAuthors = rs1.getString("Books.Authors");
newPublisher = rs1.getString("Books.Publisher");

}
statement1.close();

JSONObject first = new JSONObject();
first.put("Title", newBookTitle);
first.put("Edition", newEdition);
first.put("Courses", newCourses);
first.put("Authors", newAuthors);
first.put("Publisher", newPublisher);
first.put("ISBN", newISBN);
first.put("Date", newTransDate);

arrayObj.add(first);

}
statement0.close();

out.print(arrayObj);

}

connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>